/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;

import apus.entity.Organization;
import apus.entity.Person;
import apus.entity.PhoneNumber;
import apus.entity.RealOrganization;
import apus.entity.RealPerson;
import apus.entity.Subscriber;
import apus.exception.DatabaseException;
import apus.exception.EntityNotFoundException;
import apus.persistence.SubscriberDAO;
import apus.persistence.proxy.PhoneNumberProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Formatter;
import java.util.List;

/**
 * @author Roman Dyatkovsky
 * @author Maxim Vasilevsky
 * @since APUS v0.5
 */
public class JdbcSubscriberDAO<TypeEn extends Subscriber> extends AbstractJdbcDAO<TypeEn> implements SubscriberDAO<TypeEn> {

    @Override
    public void create(TypeEn entity) {
        entity.setId(getNextId());

        Connection cn = getConnection();
        PreparedStatement st = null;

        try {
            if (entity.getClass().equals(RealPerson.class)) {
                st = cn.prepareStatement("insert into SUBSCRIBER"
                        + " (ID_SUBSCRIBER, SUBSCRIBER_TYPE, SUBSCRIBER_NAME,"
                        + " ADDRESS, PASSPORT_NUMBER) values (?,?,?,?,?)");
                st.setInt(2, 0);
                st.setString(5, ((Person) entity).getPassportNumber());
            } else {
                st = cn.prepareStatement("insert into SUBSCRIBER"
                        + " (ID_SUBSCRIBER, SUBSCRIBER_TYPE, SUBSCRIBER_NAME,"
                        + " ADDRESS, BANKING_DETAILS) values (?,?,?,?,?)");
                st.setInt(2, 1);
                st.setString(5, ((Organization) entity).getBankingDetails());
            }

            st.setInt(1, entity.getId());
            st.setString(3, entity.getName());
            st.setString(4, entity.getAddress());

            st.execute();
            
            // create user for subscriber
            
            Formatter f = new Formatter();
            
            st = cn.prepareStatement("insert into USERS"
                        + " (ID_USER, SUBSCRIBER_ID, ROLE_ID,"
                        + " LOGIN, PASSWORD) values (?,?,?,?,?)");
            st.setInt(1, getNextUser());
            st.setInt(2, entity.getId());
            st.setInt(3, 3);
            st.setString(4, f.format("%08d", entity.getId()).toString());
            st.setString(5, "123456");
            st.execute();
            
        } catch (SQLException ex) {
            throw new DatabaseException("Can't create new subscriber with name: " + entity.getName(), ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public TypeEn read(int id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        TypeEn subscriber = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from SUBSCRIBER where ID_SUBSCRIBER=" + id);

            if (rs.next()) {
                int type = rs.getInt("SUBSCRIBER_TYPE");
                if (type == 0) {
                    subscriber = (TypeEn) new RealPerson(rs.getString("PASSPORT_NUMBER").trim(),
                            rs.getString("SUBSCRIBER_NAME").trim(), rs.getString("ADDRESS").trim());

                } else {
                    subscriber = (TypeEn) new RealOrganization(rs.getString("SUBSCRIBER_NAME").trim(),
                            rs.getString("ADDRESS").trim());
                    ((Organization) subscriber).setBankingDetails(rs.getString("BANKING_DETAILS").trim());
                }
                subscriber.setId(id);

                rs = st.executeQuery("select ID_PHONE_NUMBER from PHONE_NUMBER where SUBSCRIBER_ID=" + id);

                while (rs.next()) {
                    PhoneNumber pn = new PhoneNumberProxy();
                    pn.setId(rs.getInt("ID_PHONE_NUMBER"));
                    subscriber.addPhoneNumber(pn);
                }
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read subscriber with id=" + id, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (subscriber == null) {
            throw new EntityNotFoundException("Subscriber with id = " + id + " not found");
        }
        return subscriber;
    }

    @Override
    public void update(TypeEn entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            if (entity.getClass().equals(RealPerson.class)) {
                st = cn.prepareStatement("update SUBSCRIBER set SUBSCRIBER_TYPE=0, SUBSCRIBER_NAME=?, "
                        + " ADDRESS=?, PASSPORT_NUMBER=? where ID_SUBSCRIBER=?");

                st.setString(3, ((Person) entity).getPassportNumber());
            } else {
                st = cn.prepareStatement("update SUBSCRIBER set SUBSCRIBER_TYPE=1, SUBSCRIBER_NAME=?, "
                        + " ADDRESS=?, BANKING_DETAILS=? where ID_SUBSCRIBER=?");
                st.setString(3, ((Organization) entity).getBankingDetails());
            }

            st.setString(1, entity.getName());
            st.setString(2, entity.getAddress());
            st.setInt(4, entity.getId());

            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't update subscriber with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public void delete(TypeEn entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("delete from SUBSCRIBER where ID_SUBSCRIBER=?");
            st.setInt(1, entity.getId());
            st.execute();
            
            st = cn.prepareStatement("delete from USERS where SUBSCRIBER_ID=?");
            st.setInt(1, entity.getId());
            st.execute();
            
        } catch (SQLException e) {
            throw new DatabaseException("Can't delete subscriber with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }



    @Override
    public List<TypeEn> readByName(String name, int sType) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List<TypeEn> searchResult = new ArrayList<>();
        try {
            TypeEn subscriber;
            st = cn.createStatement();
            rs = st.executeQuery("select * from SUBSCRIBER where lower(SUBSCRIBER_NAME) "
                    + "LIKE '%" + name.toLowerCase() + "%' AND SUBSCRIBER_TYPE="+sType);

            while (rs.next()) {
                int type = rs.getInt("SUBSCRIBER_TYPE");
                if (type == 0) {
                    subscriber = (TypeEn) new RealPerson(rs.getString("PASSPORT_NUMBER").trim(),
                            rs.getString("SUBSCRIBER_NAME").trim(), rs.getString("ADDRESS").trim());

                } else {
                    subscriber = (TypeEn) new RealOrganization(rs.getString("SUBSCRIBER_NAME").trim(),
                            rs.getString("ADDRESS").trim());
                    ((Organization) subscriber).setBankingDetails(rs.getString("BANKING_DETAILS").trim());
                }
                subscriber.setId(rs.getInt("ID_SUBSCRIBER"));

                rs2 = st.executeQuery("select ID_PHONE_NUMBER from PHONE_NUMBER where SUBSCRIBER_ID=" + rs.getInt("ID_SUBSCRIBER"));

                while (rs2.next()) {
                    PhoneNumber pn = new PhoneNumberProxy();
                    pn.setId(rs2.getInt("ID_PHONE_NUMBER"));
                    subscriber.addPhoneNumber(pn);
                }
                searchResult.add(subscriber);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read subscriber with name like: " + name, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs2.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (searchResult.isEmpty()) {
            throw new EntityNotFoundException("Subscriber with name " + name + " not found");
        }
        return searchResult;
    }

    @Override
    public List<TypeEn> findAll() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
